<?php
namespace App\Http\Controllers\Admin;
use App\Models\OrderDlzwRisk;
use App\Models\MemberQnRisk;
use App\Models\NewAdmin;
use App\Models\Order;
use App\Models\OrderLease;
use App\Models\OrderLeasePartRemark;
use App\Models\OrderLog;
use App\Models\OrderRemark;
use App\Models\OrderRiskAlipay;
use App\Models\SaleUser;
use App\Models\ShopUser;
use App\Services\AuthService;
use App\Services\CommonService;
use App\Services\ExportService;
use App\Services\OrderService;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class StatisticsController extends BaseController
{

    /**
     * 推广商统计
     * @param Request $request
     * @return false|string
     */
    public function extendData(Request $request){
        $orderCreateTime = $request->input('orderCreateTime', '');
        $pageSize = $request->input('pageSize',10);
        $page = $request->input('page',1);
        $sale_user_id= $request->input('userId','');
        if($sale_user_id&&!is_array($sale_user_id)){
            $where= [
                ['sale_user_id',$sale_user_id],
            ];
        }
        if($this->users['mobile']=='15685758685'){
            $saleUser=SaleUser::where('channel_name','<>','')->whereIn('id',['68','48','47','46','45'])->select('sale_user_id','channel_name')->paginate($pageSize);
        }else if($sale_user_id&&is_array($sale_user_id)){
            $saleUser=SaleUser::where('channel_name','<>','')->whereIn('sale_user_id',$sale_user_id)->select('sale_user_id','channel_name')->paginate($pageSize);
        }else{
            $saleUser=SaleUser::where('channel_name','<>','')->where($where)->select('sale_user_id','channel_name')->paginate($pageSize);

        }

        $saleUser = $saleUser?$saleUser->toArray():[];
        foreach ($saleUser['data'] as $key=>$user){
            $wherea=[];

            if ($orderCreateTime) {
                $orderCreateTimea = explode('~', $orderCreateTime);
                $wherea[] = ['create_time', '>=', strtotime($orderCreateTimea[0])];
                $wherea[] = ['create_time', '<=',strtotime($orderCreateTimea[1])];
            }

            $saleUser['data'][$key]['uv_total']=DB::table('sp_extension_statistics')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->distinct('ip')
                ->count();

            $saleUser['data'][$key]['pv_total']=DB::table('sp_extension_statistics')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

            $uids=DB::table('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->distinct('uid')
                ->where($wherea)
                ->pluck('uid');

            $saleUser['data'][$key]['register_total']=DB::table('sp_member_2')
                ->whereIn('id',$uids)
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['register_uv_present']=$saleUser['data'][$key]['register_total']?bcdiv($saleUser['data'][$key]['register_total']*100,$saleUser['data'][$key]['uv_total'],2).'%':'0%';


            $saleUser['data'][$key]['total_order']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['not_check_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where('status',OrderService::ORDER_STATUS_SIGN_NOCHECK)
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['not_pay_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where('status',OrderService::ORDER_STATUS_SIGN_NOTPAY)
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['pay_success_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where('status',OrderService::ORDER_STATUS_PAY_SUCCESS)
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['deliver_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where('status',OrderService::ORDER_STATUS_DELIVER_SUCCESS)
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['confirm_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where('status',OrderService::ORDER_STATUS_CONFIRM_GOODS)
                ->where($wherea)
                ->count();


            $saleUser['data'][$key]['completed_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_COMPLETED)
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['cancel_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_CENCEL)
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['repayment_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_REPAYMENT)
                ->where('sale_user_id',$user['sale_user_id'])

                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['overdue_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_OVERDUE)
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['refund_number'] = Order::from('sp_order_2')
                ->whereIn('status',OrderService::getOrderStatus('orderRefund'))
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

        }
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$saleUser);
    }


    /**
     * 渠道分析
     * @param Request $request
     * @return false|string
     *
     */
    public function getChannelAnalysis(Request $request){
        $orderCreateTime = $request->input('orderCreateTime', '');
        $pageSize = $request->input('pageSize',10);
        $page = $request->input('page',1);
        $sale_user_id= $request->input('userId','');
        $risk_level= $request->input('risk_level','');

        if($sale_user_id&&!is_array($sale_user_id)){
            $where= [
                ['sale_user_id',$sale_user_id],
            ];
        }


        if($sale_user_id&&is_array($sale_user_id)){
            $saleUser=SaleUser::where('channel_name','<>','')->whereIn('sale_user_id',$sale_user_id)->select('sale_user_id','channel_name')->get();
        }else{
            $saleUser=SaleUser::where('channel_name','<>','')->where($where)->select('sale_user_id','channel_name')->get();
        }

        $saleUser = $saleUser?$saleUser->toArray():[];

        if ($orderCreateTime) {
            $orderCreateTimea = explode('~', $orderCreateTime);
            $wherea[] = ['o.create_time', '>=', strtotime($orderCreateTimea[0])];
            $wherea[] = ['o.create_time', '<=',strtotime($orderCreateTimea[1])];
        }else{
            $orderCreateTime=date('Y-m-d',strtotime('-1 month')).'~'.date('Y-m-d 23:59:59',time());
            $orderCreateTimea = explode('~', $orderCreateTime);
            $wherea[] = ['o.create_time', '>=', strtotime($orderCreateTimea[0])];
            $wherea[] = ['o.create_time', '<=',strtotime($orderCreateTimea[1])];
        }

        $data=[];

        foreach ($saleUser as &$user){

            $user['day']=date('Y-m-d',strtotime($orderCreateTimea[0])).'~'.date('Y-m-d',strtotime($orderCreateTimea[1]));

            if($risk_level){
                foreach ($risk_level as $k=>$v){
                    $uids=Order::query()->from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->leftjoin('sp_member_qnyp_risk as qr', 'qr.uid', 'o.uid')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where($wherea)
                        ->where('qr.risk_level',$v)->pluck('o.uid');

                    $uids=obToArray($uids);

                    $user['risk_level']=$v;


                    $user['total_order']=Order::from('sp_order_2 as o')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.uid',$uids)
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where($wherea)
                        ->count();

                    $user['total_pay_order']=Order::from('sp_order_2 as o')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',['30','60'])
                        ->where($wherea)
                        ->count();

                    $user['total_overdueing_order']=Order::from('sp_order_2 as o')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('o.status',39)
                        ->where($wherea)
                        ->count();

                    $user['total_backing_order']=Order::from('sp_order_2 as o')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('o.status',38)
                        ->where($wherea)
                        ->count();
                    $user['total_overdue_order']=bcadd($user['total_backing_order'],$user['total_overdueing_order']);

                    $user['first_overdue_order']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where('ol.status',4)
                        ->where('ol.which_period',2)
                        ->where($wherea)
                        ->count();

                    $user['total_overdue_order']=bcsub($user['total_order'],$user['total_pay_order']);

                    $user['order_back_rate']=bcdiv($user['total_pay_order']*100,$user['total_order'],2).'%';
                    $user['order_overdue_rate']=bcsub(100,bcdiv($user['total_pay_order']*100,$user['total_order'],2),2).'%';

                    $user['first_overdue_rate']=bcdiv($user['first_overdue_order']*100,$user['total_order'],2).'%';


                    $user['total_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('ol.begin_time','<=',time())
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where($wherea)
                        ->sum('ol.rent');

                    $amount=Order::from('sp_order_2 as o')
                        ->leftJoin('sp_order_buyout_audit as oba','oba.order_id','o.id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.uid',$uids)
                        ->where($wherea)
                        ->where('oba.check_status',1)
                        ->sum('oba.amount');

                    $total_pay_amount=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('ol.begin_time','<=',time())
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->whereIn('o.uid',$uids)
                        ->where('ol.status',1)
                        ->where($wherea)
                        ->sum('ol.rent');
                    $user['total_pay_amount']=bcadd($total_pay_amount,$amount,2);

                    $last_un_pay_amount=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('ol.begin_time','>',time())
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_BUYOUT_STATUS))
                        ->where('ol.status',0)
                        ->whereIn('o.uid',$uids)
                        ->where($wherea)
                        ->sum('ol.rent');
                    $user['expect_amount']=bcadd($user['total_pay_amount'],$last_un_pay_amount,2);


                    $user['total_pay_all_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->whereIn('o.uid',$uids)
                        ->where($wherea)
                        ->sum('ol.rent');

                    $user['total_overdueing_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('o.status',39)
                        ->where('ol.status',4)
                        ->where($wherea)
                        ->sum('ol.rent');

                    $user['total_backing_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('o.status',38)
                        ->where('ol.status',4)
                        ->where($wherea)
                        ->sum('ol.rent');

                    $user['remainder_not_due']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where('ol.begin_time','<=',time())
                        ->where('ol.begin_time','>=',strtotime('-1 month'))
                        ->where($wherea)
                        ->whereIn('o.uid',$uids)
                        ->sum('ol.rent');

                    $user['remainder_back_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where('ol.begin_time','<=',time())
                        ->where('ol.begin_time','>=',strtotime('-1 month'))
                        ->whereIn('ol.status',[0,1])
                        ->whereIn('o.uid',$uids)
                        ->where($wherea)
                        ->sum('ol.rent');

                    $user['remainder_back_rate']=bcdiv($user['remainder_back_amount']*100,$user['remainder_not_due'],2).'%';


                    $user['remainder_week_not_due']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where('ol.begin_time','<=',time())
                        ->where('ol.begin_time','>=',strtotime('-7 days'))
                        ->where($wherea)
                        ->whereIn('o.uid',$uids)
                        ->sum('ol.rent');

                    $user['remainder_week_back_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where('ol.begin_time','<=',time())
                        ->where('ol.begin_time','>=',strtotime('-7 days'))
                        ->whereIn('ol.status',[0,1])
                        ->where($wherea)
                        ->whereIn('o.uid',$uids)
                        ->sum('ol.rent');


                    $user['remainder_week_back_rate']=bcdiv($user['remainder_week_back_amount']*100,$user['remainder_week_not_due'],2).'%';


                    $user['total_overdue_amount']=bcadd($user['total_backing_amount'],$user['total_overdueing_amount'],2);

                    $user['first_overdue_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->whereIn('o.uid',$uids)
                        ->where('ol.status',4)
                        ->where('ol.which_period',2)
                        ->where($wherea)
                        ->sum('ol.rent');

                    $user['amount_back_rate']=bcdiv($user['total_pay_amount']*100,$user['total_amount'],2).'%';
                    $user['first_overdue_amount_rate']=bcdiv($user['first_overdue_amount']*100,$user['total_amount'],2).'%';

                    $user['remaining_amount']=bcsub($user['total_amount'],$user['total_pay_amount'],2);

                    $user['acquisition_price']=Order::query()->from('sp_order_2 as o')
                        ->leftjoin('sp_order_goods_2 as og', 'og.order_id', 'o.id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->whereIn('o.uid',$uids)
                        ->where($wherea)
                        ->sum('og.acquisition_price');

                    $user['principal_recovery']=bcdiv($user['total_pay_amount']*100,$user['acquisition_price'],2).'%';

                    $user['fu_principal_recovery']=bcdiv($user['expect_amount']*100,$user['acquisition_price'],2).'%';
                    $user['last_un_pay_amount']=$last_un_pay_amount;

                    $data[]=$user;
                }

            }else{

                $riskLevel=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->leftjoin('sp_member_qnyp_risk as qr', 'qr.uid', 'o.uid')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where($wherea)
                    ->distinct('ol.order_id')
                    ->select(
                        DB::raw("SUM(CASE WHEN qr.risk_level = 'A' THEN 1 ELSE 0 END) as count_A"),
                        DB::raw("SUM(CASE WHEN qr.risk_level = 'B' THEN 1 ELSE 0 END) as count_B"),
                        DB::raw("SUM(CASE WHEN qr.risk_level = 'C' THEN 1 ELSE 0 END) as count_C"),
                        DB::raw("SUM(CASE WHEN qr.risk_level = 'D' THEN 1 ELSE 0 END) as count_D")
                    )
                    ->first();

                if (empty($riskLevel['count_A'])&&empty($riskLevel['count_B'])&&empty($riskLevel['count_C'])&&empty($riskLevel['count_D'])){
                    $user['risk_level']='A:0;B:0;C:0;D:0';
                }else{
                    $user['risk_level']='A:'.$riskLevel['count_A'].'; B:'.$riskLevel['count_B'].'; C:'.$riskLevel['count_C'].'; D:'.$riskLevel['count_D'];
                }

                $user['total_order']=Order::from('sp_order_2 as o')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where($wherea)
                    ->count();

                $user['total_pay_order']=Order::from('sp_order_2 as o')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',['30','60'])
                    ->where($wherea)
                    ->count();

                $user['total_overdueing_order']=Order::from('sp_order_2 as o')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('o.status',39)
                    ->where($wherea)
                    ->count();

                $user['total_backing_order']=Order::from('sp_order_2 as o')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('o.status',38)
                    ->where($wherea)
                    ->count();
                $user['total_overdue_order']=bcadd($user['total_backing_order'],$user['total_overdueing_order']);

                $user['first_overdue_order']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.status',4)
                    ->where('ol.which_period',2)
                    ->where($wherea)
                    ->count();

                $user['total_overdue_order']=bcsub($user['total_order'],$user['total_pay_order']);

                $user['order_back_rate']=bcdiv($user['total_pay_order']*100,$user['total_order'],2).'%';
                $user['first_overdue_rate']=bcdiv($user['first_overdue_order']*100,$user['total_order'],2).'%';
                $user['order_overdue_rate']=bcsub(100,bcdiv($user['total_pay_order']*100,$user['total_order'],2),2).'%';


                $user['total_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('ol.begin_time','<=',time())
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where($wherea)
                    ->sum('ol.rent');

                $amount=Order::from('sp_order_2 as o')
                    ->leftJoin('sp_order_buyout_audit as oba','oba.order_id','o.id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where($wherea)
                    ->where('oba.check_status',1)
                    ->sum('oba.amount');

                $total_pay_amount=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('ol.begin_time','<=',time())
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.status',1)
                    ->where($wherea)
                    ->sum('ol.rent');
                $user['total_pay_amount']=bcadd($total_pay_amount,$amount,2);

                $last_un_pay_amount=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('ol.begin_time','>',time())
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_BUYOUT_STATUS))
                    ->where('ol.status',0)
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['expect_amount']=bcadd($user['total_pay_amount'],$last_un_pay_amount,2);

                $user['last_un_pay_amount']=$last_un_pay_amount;

                $user['total_pay_all_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['total_overdueing_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('o.status',39)
                    ->where('ol.status',4)
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['total_backing_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('o.status',38)
                    ->where('ol.status',4)
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['total_overdue_amount']=bcadd($user['total_backing_amount'],$user['total_overdueing_amount'],2);

                $user['first_overdue_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.status',4)
                    ->where('ol.which_period',2)
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['amount_back_rate']=bcdiv($user['total_pay_amount']*100,$user['total_amount'],2).'%';
                $user['first_overdue_amount_rate']=bcdiv($user['first_overdue_amount']*100,$user['total_amount'],2).'%';

                $user['remaining_amount']=bcsub($user['total_amount'],$user['total_pay_amount'],2);

                $user['remainder_not_due']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.begin_time','<=',time())
                    ->where('ol.begin_time','>=',strtotime('-1 month'))
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['remainder_back_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.begin_time','<=',time())
                    ->where('ol.begin_time','>=',strtotime('-1 month'))
                    ->whereIn('ol.status',[0,1])
                    ->where($wherea)
                    ->sum('ol.rent');


                $user['remainder_back_rate']=bcdiv($user['remainder_back_amount']*100,$user['remainder_not_due'],2).'%';

                $user['remainder_week_not_due']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.begin_time','<=',time())
                    ->where('ol.begin_time','>=',strtotime('-7 days'))
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['remainder_week_back_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.begin_time','<=',time())
                    ->where('ol.begin_time','>=',strtotime('-7 days'))
                    ->whereIn('ol.status',[0,1])
                    ->where($wherea)
                    ->sum('ol.rent');


                $user['remainder_week_back_rate']=bcdiv($user['remainder_week_back_amount']*100,$user['remainder_week_not_due'],2).'%';

                $user['acquisition_price']=Order::query()->from('sp_order_2 as o')
                    ->leftjoin('sp_order_goods_2 as og', 'og.order_id', 'o.id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where($wherea)
                    ->sum('og.acquisition_price');

                $user['principal_recovery']=bcdiv($user['total_pay_amount']*100,$user['acquisition_price'],2).'%';

                $user['fu_principal_recovery']=bcdiv($user['expect_amount']*100,$user['acquisition_price'],2).'%';

                $data[]=$user;

            }
        }
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$data);
    }

    //还款统计表
    public function getRepaymentStatistics(Request $request)
    {
        $orderCreateTime = $request->input('orderCreateTime', '');
        $pageSize = $request->input('pageSize', 10);
        $page = $request->input('page', 1);

        if($orderCreateTime){
            $orderCreateTimea = explode('~', $orderCreateTime);
            $days = new \DatePeriod(
                new \DateTime($orderCreateTimea[0]),
                new \DateInterval('P1D'),
                new \DateTime($orderCreateTimea[1])
            );
        }else{
            $days = new \DatePeriod(
                new \DateTime(date('Y-m-d',strtotime('-1 week'))),
                new \DateInterval('P1D'),
                new \DateTime(date('Y-m-d',time()+86400))
            );
        }

        $data=[];
        foreach ($days as $key=>$today){
            $day=$today->format('Y-m-d');
            $wherea=[];
            $wherea[] = ['ol.begin_time', '>=', strtotime($day)];
            $wherea[] = ['ol.begin_time', '<=',strtotime($day)+86399];
            $data[$key]['day']=$today->format('Y-m-d');

            $data[$key]['count_a']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
                ->where($wherea)
                ->count();

            $data[$key]['count_baa']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->where('o.status',60)
                ->where('ol.which_period',2)
                ->where('ol.status',0)
                ->where($wherea)
                ->count();
            $data[$key]['count_ba']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.which_period',2)
                    ->where('ol.status',1)
                    ->where('ol.pay_time','<',strtotime($day))
                    ->where($wherea)
                    ->count()+$data[$key]['count_baa'];
            $data[$key]['count_ca']=bcdiv($data[$key]['count_ba']*100,$data[$key]['count_a'],2).'%';
            $data[$key]['count_b']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.which_period',2)
                    ->where('ol.status',1)
                    ->where('ol.pay_time','>=',strtotime($day))
                    ->where('ol.pay_time','<=',strtotime($day)+86399)
                    ->where($wherea)
                    ->count()+$data[$key]['count_ba'];
            $data[$key]['count_b']=round($data[$key]['count_b'],2);
            $data[$key]['count_c']=bcdiv($data[$key]['count_b']*100,$data[$key]['count_a'],2).'%';
            $data[$key]['count_d']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.which_period',2)
                    ->where('ol.status',1)
                    ->where($wherea)
                    ->count()+$data[$key]['count_baa'];
            $data[$key]['count_e']=bcdiv($data[$key]['count_d']*100,$data[$key]['count_a'],2).'%';
            $data[$key]['count_fa']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
//                ->where('ol.overdue_days','<=',1)
                ->where('ol.overdue_days','>',0)
                ->where($wherea)
                ->count();
            $data[$key]['count_ga']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
                ->where('ol.overdue_days','<=',1)
                ->where('ol.overdue_days','>',0)
                ->where('ol.status',1)
                ->where($wherea)
                ->count();
            $data[$key]['count_ha']=bcdiv($data[$key]['count_ga']*100,$data[$key]['count_fa'],2).'%';

            $data[$key]['count_f']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
//                ->where('ol.overdue_days','>=',3)
                ->where('ol.overdue_days','>',1)
                ->where($wherea)
                ->count();
            $data[$key]['count_g']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
                ->where('ol.overdue_days','<=',3)
                ->where('ol.overdue_days','>',1)
                ->where('ol.status',1)
                ->where($wherea)
                ->count();
            $data[$key]['count_h']=bcdiv($data[$key]['count_g']*100,$data[$key]['count_f'],2).'%';
            $data[$key]['count_i']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
//                ->where('ol.overdue_days','<=',7)
                ->where('ol.overdue_days','>',3)
                ->where($wherea)
                ->count();
            $data[$key]['count_j']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
                ->where('ol.overdue_days','<=',7)
                ->where('ol.overdue_days','>',3)
                ->where('ol.status',1)
                ->where($wherea)
                ->count();
            $data[$key]['count_k']=bcdiv($data[$key]['count_j']*100,$data[$key]['count_i'],2).'%';
            $data[$key]['count_l']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
//                ->where('ol.overdue_days','<=',30)
                ->where('ol.overdue_days','>',7)
                ->where($wherea)
                ->count();
            $data[$key]['count_m']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
                ->where('ol.overdue_days','<=',30)
                ->where('ol.overdue_days','>',7)
                ->where('ol.status',1)
                ->where($wherea)
                ->count();
            $data[$key]['count_n']=bcdiv($data[$key]['count_m']*100,$data[$key]['count_l'],2).'%';

            $data[$key]['amount_a']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
                ->where($wherea)
                ->sum('ol.rent');

            $data[$key]['amount_baa']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->leftJoin('sp_order_buyout_audit as oba','oba.order_id','o.id')
                ->where('o.status',60)
                ->where('ol.which_period',2)
                ->where('ol.status',0)
                ->where($wherea)
                ->sum('oba.amount');

            $data[$key]['amount_ba']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.which_period',2)
                    ->where('ol.status',1)
                    ->where('ol.pay_time','<=',strtotime($day))
                    ->where($wherea)
                    ->sum('ol.rent')+$data[$key]['amount_baa'];
            $data[$key]['amount_ba']=round($data[$key]['amount_ba'],2);

            $data[$key]['amount_ca']=bcdiv($data[$key]['amount_ba']*100,$data[$key]['amount_a'],2).'%';

            $data[$key]['amount_b']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.which_period',2)
                    ->where('ol.status',1)
                    ->where('ol.pay_time','>=',strtotime($day))
                    ->where('ol.pay_time','<=',strtotime($day)+86399)
                    ->where($wherea)
                    ->sum('ol.rent')+$data[$key]['amount_ba'];

            $data[$key]['amount_b']=round($data[$key]['amount_b'],2);

            $data[$key]['amount_c']=bcdiv($data[$key]['amount_b']*100,$data[$key]['amount_a'],2).'%';

            $data[$key]['amount_d']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.which_period',2)
                    ->where('ol.status',1)
                    ->where($wherea)
                    ->sum('ol.rent')+$data[$key]['amount_baa'];
            $data[$key]['amount_d']=round($data[$key]['amount_d'],2);
            $data[$key]['amount_e']=bcdiv($data[$key]['amount_d']*100,$data[$key]['amount_a'],2).'%';
            $data[$key]['amount_fa']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
//                ->where('ol.overdue_days','<=',1)
                ->where('ol.overdue_days','>',0)
                ->where($wherea)
                ->sum('ol.rent');
            $data[$key]['amount_ga']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
                ->where('ol.overdue_days','<=',1)
                ->where('ol.overdue_days','>',0)
                ->where('ol.status',1)
                ->where($wherea)
                ->sum('ol.rent');
            $data[$key]['amount_ha']=bcdiv($data[$key]['amount_ga']*100,$data[$key]['amount_fa'],2).'%';
            $data[$key]['amount_f']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
//                ->where('ol.overdue_days','<=',3)
                ->where('ol.overdue_days','>',1)
                ->where($wherea)
                ->sum('ol.rent');
            $data[$key]['amount_g']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
                ->where('ol.overdue_days','<=',3)
                ->where('ol.overdue_days','>',1)
                ->where('ol.status',1)
                ->where($wherea)
                ->sum('ol.rent');
            $data[$key]['amount_h']=bcdiv($data[$key]['amount_g']*100,$data[$key]['amount_f'],2).'%';

            $data[$key]['amount_i']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
//                ->where('ol.overdue_days','<=',7)
                ->where('ol.overdue_days','>',3)
                ->where($wherea)
                ->sum('ol.rent');
            $data[$key]['amount_j']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
                ->where('ol.overdue_days','<=',7)
                ->where('ol.overdue_days','>',3)
                ->where('ol.status',1)
                ->where($wherea)
                ->sum('ol.rent');
            $data[$key]['amount_k']=bcdiv($data[$key]['amount_j']*100,$data[$key]['amount_i'],2).'%';
            $data[$key]['amount_l']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
//                ->where('ol.overdue_days','<=',30)
                ->where('ol.overdue_days','>',7)
                ->where($wherea)
                ->sum('ol.rent');
            $data[$key]['amount_m']=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                ->where('ol.which_period',2)
                ->where('ol.overdue_days','<=',30)
                ->where('ol.overdue_days','>',7)
                ->where('ol.status',1)
                ->where($wherea)
                ->sum('ol.rent');
            $data[$key]['amount_n']=bcdiv($data[$key]['amount_m']*100,$data[$key]['amount_l'],2).'%';

        }

        rsort($data);

        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$data);


    }

    /**
     * 列表导出
     * @param Request $request
     * @return false|string
     */
    public function extendDataExport(Request $request){
        $orderCreateTime = $request->input('orderCreateTime', '');
        $pageSize = $request->input('pageSize',10);
        $page = $request->input('page',1);
        $sale_user_id= $request->input('userId','');
        if($sale_user_id&&!is_array($sale_user_id)){
            $where= [
                ['sale_user_id',$sale_user_id],
            ];
        }
        if($this->users['mobile']=='15685758685'){
            $saleUser=SaleUser::where('channel_name','<>','')->whereIn('id',['68','48','47','46','45'])->select('sale_user_id','channel_name')->paginate($pageSize);
        }else if($sale_user_id&&is_array($sale_user_id)){
            $saleUser=SaleUser::where('channel_name','<>','')->whereIn('sale_user_id',$sale_user_id)->select('sale_user_id','channel_name')->paginate($pageSize);
        }else{
            $saleUser=SaleUser::where('channel_name','<>','')->where($where)->select('sale_user_id','channel_name')->paginate($pageSize);

        }

        $saleUser = $saleUser?$saleUser->toArray():[];
        foreach ($saleUser['data'] as $key=>$user){
            $wherea=[];

            if ($orderCreateTime) {
                $orderCreateTimea = explode('~', $orderCreateTime);
                $wherea[] = ['create_time', '>=', strtotime($orderCreateTimea[0])];
                $wherea[] = ['create_time', '<=',strtotime($orderCreateTimea[1])];
            }


            $saleUser['data'][$key]['pv_total']=DB::table('sp_extension_statistics')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['uv_total']=DB::table('sp_extension_statistics')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->distinct('ip')
                ->count();

            $uids=DB::table('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->distinct('uid')
                ->where($wherea)
                ->pluck('uid');

            $saleUser['data'][$key]['register_total']=DB::table('sp_member_2')
                ->whereIn('id',$uids)
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['register_uv_present']=$saleUser['data'][$key]['register_total']?bcdiv($saleUser['data'][$key]['register_total']*100,$saleUser['data'][$key]['uv_total'],2).'%':'0%';


            $saleUser['data'][$key]['total_order']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['not_check_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where('status',OrderService::ORDER_STATUS_SIGN_NOCHECK)
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['not_pay_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where('status',OrderService::ORDER_STATUS_SIGN_NOTPAY)
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['pay_success_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where('status',OrderService::ORDER_STATUS_PAY_SUCCESS)
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['deliver_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where('status',OrderService::ORDER_STATUS_DELIVER_SUCCESS)
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['confirm_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$user['sale_user_id'])
                ->where('status',OrderService::ORDER_STATUS_CONFIRM_GOODS)
                ->where($wherea)
                ->count();


            $saleUser['data'][$key]['completed_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_COMPLETED)
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['cancel_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_CENCEL)
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['repayment_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_REPAYMENT)
                ->where('sale_user_id',$user['sale_user_id'])

                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['overdue_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_OVERDUE)
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

            $saleUser['data'][$key]['refund_number'] = Order::from('sp_order_2')
                ->whereIn('status',OrderService::getOrderStatus('orderRefund'))
                ->where('sale_user_id',$user['sale_user_id'])
                ->where($wherea)
                ->count();

        }
        $data=$saleUser['data'];
        if(!$data){
            return ReturnMsg(CommonService::HTTP_CODE_FAIL,'无数据');
        }
        $params = $request->input();

        // dd($data);
        ExportService::exportFile($params,$data,ExportService::extend_data_export);
    }


    //个人数据详情
    public function orderExendDataDetail(Request $request){

        $orderCreateTime = $request->input('orderCreateTime', '');
        $sale_user_id= $request->input('sale_user_id',0);
        if($orderCreateTime){
            $orderCreateTimea = explode('~', $orderCreateTime);
            $days = new \DatePeriod(
                new \DateTime($orderCreateTimea[0]),
                new \DateInterval('P1D'),
                new \DateTime($orderCreateTimea[1])
            );
        }else{
            $days = new \DatePeriod(
                new \DateTime(date('Y-m-d',strtotime('-1 week'))),
                new \DateInterval('P1D'),
                new \DateTime(date('Y-m-d',time()+86400))
            );
        }

        $data=[];
        foreach ($days as $key=>$today){
            $day=$today->format('Y-m-d');
            $wherea=[];
            $data[$key]['day']=$today->format('Y-m-d');


            $wherea[] = ['create_time', '>=', strtotime($day)];
            $wherea[] = ['create_time', '<=',strtotime($day)+86400];


            $data[$key]['uv_total']=DB::table('sp_extension_statistics')
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->distinct('ip')
                ->count();

            $data[$key]['pv_total']=DB::table('sp_extension_statistics')
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

            $data[$key]['register_total']=DB::table('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->distinct('uid')
                ->where($wherea)
                ->count();

            $uids=DB::table('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->distinct('uid')
                ->where($wherea)
                ->pluck('uid');

            $data[$key]['register_total']=DB::table('sp_member_2')
                ->whereIn('id',$uids)
                ->where($wherea)
                ->count();

            $data[$key]['register_uv_present']=$data[$key]['register_total']?bcdiv($data[$key]['register_total']*100,$data[$key]['uv_total'],2).'%':'0%';


            $data[$key]['total_order']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

            $data[$key]['not_check_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where('status',OrderService::ORDER_STATUS_SIGN_NOCHECK)
                ->where($wherea)
                ->count();

            $data[$key]['not_pay_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where('status',OrderService::ORDER_STATUS_SIGN_NOTPAY)
                ->where($wherea)
                ->count();

            $data[$key]['pay_success_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where('status',OrderService::ORDER_STATUS_PAY_SUCCESS)
                ->where($wherea)
                ->count();

            $data[$key]['deliver_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where('status',OrderService::ORDER_STATUS_DELIVER_SUCCESS)
                ->where($wherea)
                ->count();

            $data[$key]['confirm_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where('status',OrderService::ORDER_STATUS_CONFIRM_GOODS)
                ->where($wherea)
                ->count();


            $data[$key]['completed_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_COMPLETED)
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

            $data[$key]['cancel_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_CENCEL)
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

            $data[$key]['repayment_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_REPAYMENT)
                ->where('sale_user_id',$sale_user_id)

                ->where($wherea)
                ->count();

            $data[$key]['overdue_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_OVERDUE)
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

            $data[$key]['refund_number'] = Order::from('sp_order_2')
                ->whereIn('status',OrderService::getOrderStatus('orderRefund'))
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

        }
        rsort($data);
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$data);
    }

    public function orderExendDataDetailExport(Request $request)
    {
        $orderCreateTime = $request->input('orderCreateTime', '');
        $sale_user_id= $request->input('sale_user_id',0);
        if($orderCreateTime){
            $orderCreateTimea = explode('~', $orderCreateTime);
            $days = new \DatePeriod(
                new \DateTime($orderCreateTimea[0]),
                new \DateInterval('P1D'),
                new \DateTime($orderCreateTimea[1])
            );
        }else{
            $days = new \DatePeriod(
                new \DateTime(date('Y-m-d',strtotime('-1 week'))),
                new \DateInterval('P1D'),
                new \DateTime(date('Y-m-d',time()+86400))
            );
        }

        $data=[];
        foreach ($days as $key=>$today){
            $day=$today->format('Y-m-d');
            $wherea=[];
            $data[$key]['day']=$today->format('Y-m-d');


            $wherea[] = ['create_time', '>=', strtotime($day)];
            $wherea[] = ['create_time', '<=',strtotime($day)+86400];



            $data[$key]['pv_total']=DB::table('sp_extension_statistics')
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

            $data[$key]['uv_total']=DB::table('sp_extension_statistics')
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->distinct('ip')
                ->count();


            $data[$key]['register_total']=DB::table('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->distinct('uid')
                ->where($wherea)
                ->count();

            $uids=DB::table('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->distinct('uid')
                ->where($wherea)
                ->pluck('uid');

            $data[$key]['register_total']=DB::table('sp_member_2')
                ->whereIn('id',$uids)
                ->where($wherea)
                ->count();

            $data[$key]['register_uv_present']=$data[$key]['register_total']?bcdiv($data[$key]['register_total']*100,$data[$key]['uv_total'],2).'%':'0%';


            $data[$key]['total_order']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

            $data[$key]['not_check_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where('status',OrderService::ORDER_STATUS_SIGN_NOCHECK)
                ->where($wherea)
                ->count();

            $data[$key]['not_pay_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where('status',OrderService::ORDER_STATUS_SIGN_NOTPAY)
                ->where($wherea)
                ->count();

            $data[$key]['pay_success_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where('status',OrderService::ORDER_STATUS_PAY_SUCCESS)
                ->where($wherea)
                ->count();

            $data[$key]['deliver_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where('status',OrderService::ORDER_STATUS_DELIVER_SUCCESS)
                ->where($wherea)
                ->count();

            $data[$key]['confirm_number']=Order::from('sp_order_2')
                ->where('sale_user_id',$sale_user_id)
                ->where('status',OrderService::ORDER_STATUS_CONFIRM_GOODS)
                ->where($wherea)
                ->count();


            $data[$key]['completed_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_COMPLETED)
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

            $data[$key]['cancel_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_CENCEL)
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

            $data[$key]['repayment_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_REPAYMENT)
                ->where('sale_user_id',$sale_user_id)

                ->where($wherea)
                ->count();

            $data[$key]['overdue_number'] = Order::from('sp_order_2')
                ->where('status',OrderService::ORDER_STATUS_OVERDUE)
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

            $data[$key]['refund_number'] = Order::from('sp_order_2')
                ->whereIn('status',OrderService::getOrderStatus('orderRefund'))
                ->where('sale_user_id',$sale_user_id)
                ->where($wherea)
                ->count();

        }
        rsort($data);
        if(!$data){
            return ReturnMsg(CommonService::HTTP_CODE_FAIL,'无数据');
        }
        $params = $request->input();

        ExportService::exportFile($params,$data,ExportService::extend_data_export_detail);
    }


    /**
     * 审核数据统计
     * @param Request $request
     * @return false|string
     */
    public function orderCheckData(Request $request){
        $orderCreateTime = $request->input('orderCreateTime', '');
        $pageSize = $request->input('pageSize',10);
        $page = $request->input('page',1);
        $userId = $request->input('userId','');
        $username = $request->input('username','');
        $where= [
            ['admin.is_delete',CommonService::IS_DELETE_NOT],
        ];
        if($userId != ''){
            $where[] = ['admin.user_id',$userId==0?0:ShopUser::where('id',$userId)->value('member_id')];
        }else{
            $where[] = ['admin.user_id',0];
        }
        if($username){
            $where[] = ['admin.username','like','%'.$username.'%'];
        }
        $adminUser = NewAdmin::from('sp_other_admin as admin')
            ->where($where)
            ->leftjoin('sp_other_auth_group as group','admin.role_id','=','group.id')
            ->select('admin.id','admin.mobile','admin.username','admin.create_time','admin.status','admin.type','admin.user_id','admin.role_id','group.title','group.rules')
            ->orderBy('admin.id','desc')
            ->paginate($pageSize);
        $adminUser = $adminUser?$adminUser->toArray():[];

        foreach ($adminUser['data'] as $key=>$user){
            $wherea=[];
            $whereb=[];
            $adminUser['data'][$key]['pass_number']=(new OrderLog)->getCountNumber($user['id'],$orderCreateTime);
            $adminUser['data'][$key]['check_number']=(new OrderLog)->getCheckDataNumber($user['id'],$orderCreateTime);
            $adminUser['data'][$key]['return_number']=$adminUser['data'][$key]['check_number']-$adminUser['data'][$key]['pass_number'];
            $shopUser = ShopUser::where(['member_id'=>$user['user_id'],'is_delete'=>ShopUser::SHOP_USER_NOT_DELETE])->first();
            $shopUser = obToArray($shopUser);
            $adminUser['data'][$key]['admin_name'] = $shopUser['shopname'];
            if ($orderCreateTime) {
                $orderCreateTimea = explode('~', $orderCreateTime);
                $wherea[] = ['o.create_time', '>=', strtotime($orderCreateTimea[0])];
                $wherea[] = ['o.create_time', '<=',strtotime($orderCreateTimea[1])];
            }
            $wherec=[];
            if ($orderCreateTime) {
                $orderCreateTimeb = explode('~', $orderCreateTime);
                $whereb[] = ['log.create_time', '>=', $orderCreateTimeb[0]];
                $whereb[] = ['log.create_time', '<=', $orderCreateTimeb[1]];

                $wherec[] = ['create_time', '>=', $orderCreateTimeb[0]];
                $wherec[] = ['create_time', '<=', $orderCreateTimeb[1]];
            }
//            $adminUser['data'][$key]['not_check_number'] = Order::from('sp_order_2 as o')
//                ->leftjoin('sp_order_log as log', 'o.id', 'log.order_id')
//                ->where('log.status',OrderLog::ORDER_LOG_PROCESS)
//                ->where('o.status',OrderService::ORDER_STATUS_SIGN_NOCHECK)
//                ->distinct('log.order_id')
//                ->where($whereb)
//                ->where('log.user_id',$user['id'])
//                ->count();

            $process_order_ids=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_log as log', 'o.id', 'log.order_id')
                ->where('log.status',OrderLog::ORDER_LOG_PROCESS)
                ->where('o.status',OrderService::ORDER_STATUS_SIGN_NOCHECK)
                ->distinct('log.order_id')
                ->where($whereb)
                ->where('log.user_id',$user['id'])
                ->pluck('log.order_id');

            $process_orders=OrderLog::where('status','<>',OrderLog::ORDER_LOG_PROCESS)
                ->where($wherec)
                ->distinct('order_id')
                ->whereIn('order_id',$process_order_ids)
                ->count();
            $adminUser['data'][$key]['not_check_number']= count($process_order_ids)-$process_orders;

            $adminUser['data'][$key]['not_pay_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_SIGN_NOTPAY)
                ->distinct('re.order_id')
                ->where($wherea)
                ->where('re.uid',$user['id'])
                ->count();

            $adminUser['data'][$key]['pay_success_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_PAY_SUCCESS)
                ->distinct('re.order_id')
                ->where('re.uid',$user['id'])
                ->where($wherea)
                ->count();
            $adminUser['data'][$key]['deliver_success_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_DELIVER_SUCCESS)
                ->distinct('re.order_id')
                ->where('re.uid',$user['id'])
                ->where($wherea)
                ->count();
            $adminUser['data'][$key]['goods_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_CONFIRM_GOODS)
                ->where('re.uid',$user['id'])
                ->distinct('re.order_id')
                ->where($wherea)
                ->count();
            $adminUser['data'][$key]['completed_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_COMPLETED)
                ->distinct('re.order_id')
                ->where('re.uid',$user['id'])
                ->where($wherea)
                ->count();
            $adminUser['data'][$key]['cancel_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_CENCEL)
                ->distinct('re.order_id')
                ->where('re.uid',$user['id'])
                ->where($wherea)
                ->count();
            $adminUser['data'][$key]['repayment_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_REPAYMENT)
                ->distinct('re.order_id')
                ->where('re.uid',$user['id'])
                ->where($wherea)
                ->count();
            $adminUser['data'][$key]['overdue_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_OVERDUE)
                ->distinct('re.order_id')
                ->where('re.uid',$user['id'])
                ->where($wherea)
                ->count();
            $adminUser['data'][$key]['refund_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->whereIn('o.status',OrderService::getOrderStatus('orderRefund'))
                ->distinct('re.order_id')
                ->where('re.uid',$user['id'])
                ->where($wherea)
                ->count();


        }
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$adminUser);
    }

    //个人数据详情
    public function orderCheckDataDetail(Request $request){

        $orderCreateTime = $request->input('orderCreateTime', '');
        $id= $request->input('id',0);
        $user_id= $request->input('user_id',0);
        if($orderCreateTime){
            $orderCreateTimea = explode('~', $orderCreateTime);
            $days = new \DatePeriod(
                new \DateTime($orderCreateTimea[0]),
                new \DateInterval('P1D'),
                new \DateTime($orderCreateTimea[1])
            );
        }else{
            $days = new \DatePeriod(
                new \DateTime(date('Y-m-d',strtotime('-1 week'))),
                new \DateInterval('P1D'),
                new \DateTime(date('Y-m-d',time()))
            );
        }

        $data=[];
        foreach ($days as $key=>$today){
            $orderCreateTime=$today->format('Y-m-d').'~'.$today->format('Y-m-d').' 23-59-59';
            $day=$today->format('Y-m-d');
            $wherea=[];
            $whereb=[];
            $data[$key]['day']=$today->format('Y-m-d');
            $data[$key]['pass_number']=(new OrderLog)->getCountNumber($id,$orderCreateTime);
            $data[$key]['check_number']=(new OrderLog)->getCheckDataNumber($id,$orderCreateTime);
            $data[$key]['return_number']=$data[$key]['check_number']-$data[$key]['pass_number'];
            $shopUser = ShopUser::where(['member_id'=>$user_id,'is_delete'=>ShopUser::SHOP_USER_NOT_DELETE])->first();
            $shopUser = obToArray($shopUser);
            $data[$key]['admin_name'] = $shopUser['shopname'];

            $wherea[] = ['o.create_time', '>=', strtotime($day)];
            $wherea[] = ['o.create_time', '<=',strtotime($day)+86400];

            $whereb[] = ['log.create_time', '>=', strtotime($day)];
            $whereb[] = ['log.create_time', '<=', strtotime($day)+86400];

            $data[$key]['not_check_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_log as log', 'o.id', 'log.order_id')
                ->where('log.status',OrderLog::ORDER_LOG_PROCESS)
                ->where('o.status',OrderService::ORDER_STATUS_SIGN_NOCHECK)
                ->distinct('log.order_id')
                ->where($whereb)
                ->where('log.user_id',$id)
                ->count();


            $process_order_ids=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_log as log', 'o.id', 'log.order_id')
                ->where('log.status',OrderLog::ORDER_LOG_PROCESS)
                ->where('o.status',OrderService::ORDER_STATUS_SIGN_NOCHECK)
                ->distinct('log.order_id')
                ->where($whereb)
                ->where('log.user_id',$id)
                ->pluck('log.order_id');
            $wherec[] = ['create_time', '>=', strtotime($day)];
            $wherec[] = ['create_time', '<=', strtotime($day)+86400];
            $process_orders=OrderLog::where('status','<>',OrderLog::ORDER_LOG_PROCESS)
                ->distinct('order_id')
                ->where($wherec)
                ->whereIn('order_id',$process_order_ids)
                ->count();

            $data[$key]['not_check_number'] = count($process_order_ids)-$process_orders;

            $data[$key]['not_pay_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_SIGN_NOTPAY)
                ->distinct('re.order_id')
                ->where($wherea)
                ->where('re.uid',$id)
                ->count();

            $data[$key]['pay_success_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_PAY_SUCCESS)
                ->distinct('re.order_id')
                ->where('re.uid',$id)
                ->where($wherea)
                ->count();
            $data[$key]['deliver_success_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_DELIVER_SUCCESS)
                ->distinct('re.order_id')
                ->where('re.uid',$id)
                ->where($wherea)
                ->count();
            $data[$key]['goods_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_CONFIRM_GOODS)
                ->where('re.uid',$id)
                ->distinct('re.order_id')
                ->where($wherea)
                ->count();
            $data[$key]['completed_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_COMPLETED)
                ->distinct('re.order_id')
                ->where('re.uid',$id)
                ->where($wherea)
                ->count();
            $data[$key]['cancel_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_CENCEL)
                ->distinct('re.order_id')
                ->where('re.uid',$id)
                ->where($wherea)
                ->count();
            $data[$key]['repayment_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_REPAYMENT)
                ->distinct('re.order_id')
                ->where('re.uid',$id)
                ->where($wherea)
                ->count();
            $data[$key]['overdue_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->where('o.status',OrderService::ORDER_STATUS_OVERDUE)
                ->distinct('re.order_id')
                ->where('re.uid',$id)
                ->where($wherea)
                ->count();
            $data[$key]['refund_number'] = Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_remark_2 as re', 're.order_id', 'o.id')
                ->leftjoin('sp_order_log as log', 're.order_id', 'log.order_id')
                ->whereRaw('re.uid=log.user_id')
                ->where('content','订单审核通过')
                ->whereIn('o.status',OrderService::getOrderStatus('orderRefund'))
                ->distinct('re.order_id')
                ->where('re.uid',$id)
                ->where($wherea)
                ->count();
        }
        rsort($data);
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$data);
    }


    //个人数据详情
    public function orderEveryDayList(Request $request){

        $unit_price= $request->input('unit_price');
        $price= $request->input('price');

        if(isset($user['password'])&&!empty($user['password'])){
            $userToken = $request->header("Authorization");
            $user = NewAdmin::where(['token'=>$userToken,'type'=>AuthService::MODEL_ADMIN])->first();
            $user = obToArray($user);
            if(md5($user['mobile'].$request['password']) != $user['password']){
                return ReturnMsg(CommonService::HTTP_CODE_FAIL,'密码输入错误错误');
            }
        }


        $orderCreateTime = $request->input('orderCreateTime', '');
        if($orderCreateTime){
            $orderCreateTimea = explode('~', $orderCreateTime);
            $days = new \DatePeriod(
                new \DateTime($orderCreateTimea[0]),
                new \DateInterval('P1D'),
                new \DateTime($orderCreateTimea[1])
            );
        }else{
            $days = new \DatePeriod(
                new \DateTime(date('Y-m-d',strtotime('-1 week'))),
                new \DateInterval('P1D'),
                new \DateTime(date('Y-m-d',strtotime('+1 day')))
            );
        }



        $sale_user_ids=SaleUser::where('channel_name','<>','')->whereIn('id',['68','48','47','46','45'])->pluck('sale_user_id');



        $data=[];
        foreach ($days as $key=>$today){
            $day=$today->format('Y-m-d');
            $wherea=[];
            $whereb=[];
            $wherea[] = ['create_time', '>=', strtotime($day)];
            $wherea[] = ['create_time', '<=',strtotime($day)+86400];

            $whereb[] = ['created_at', '>=', $day];
            $whereb[] = ['created_at', '<=',date('Y-m-d H:i:s',strtotime($day)+86400)];

            $data[$key]['day']=$today->format('Y-m-d');
            $data[$key]['channel_name']='fql';


            $data[$key]['uv_total']=DB::table('sp_extension_statistics')
                ->whereIn('sale_user_id',$sale_user_ids)
                ->where($wherea)
                ->distinct('ip')
                ->count();


            $order_ides=DB::table('sp_order_2')
                ->whereIn('sale_user_id',$sale_user_ids)
                ->orderBy('id','asc')
                ->distinct('uid')
                ->where($wherea)
                ->pluck('id');

            $data[$key]['register_total']=count($order_ides);

//            $data[$key]['register_total']=DB::table('sp_member_2')
//                ->whereIn('id',$uids)
//                ->where($wherea)
//                ->count();

            $data[$key]['register_uv_present']=$data[$key]['register_total']?bcdiv($data[$key]['register_total']*100,$data[$key]['uv_total'],2).'%':'0%';


            $data[$key]['push_order_total']=DB::table('sp_other_order')
                ->whereIn('order_id',$order_ides)
                ->where($whereb)
                ->count();

            $data[$key]['push_uv_present']= $data[$key]['uv_total']?bcdiv($data[$key]['push_order_total']*100, $data[$key]['uv_total'],2).'%':'0%';

            $data[$key]['push_register_present']=$data[$key]['register_total']?bcdiv($data[$key]['push_order_total']*100,$data[$key]['register_total'],2).'%':'0%';
            $data[$key]['uv_register_present']=bcdiv($data[$key]['uv_total'],$data[$key]['push_order_total'],2);

            $data[$key]['countPrice']=$unit_price&&$price?bcmul($unit_price-$data[$key]['uv_register_present']*$price,$data[$key]['push_order_total'],2):'';

        }
//    }
        rsort($data);
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$data);
    }

    //个人数据详情
    public function orderEveryDayDetail(Request $request){

        $day = $request->input('day', date('Y-m-d'));

        $sale_user_ids=SaleUser::where('channel_name','<>','')->whereIn('id',['68','48','47','46','45'])->select('sale_user_id','channel_name')->get()->toArray();


        $data=[];
        foreach ($sale_user_ids as $key=>$sale){
            $wherea=[];
            $whereb=[];
            $wherea[] = ['create_time', '>=', strtotime($day)];
            $wherea[] = ['create_time', '<=',strtotime($day)+86400];

            $whereb[] = ['created_at', '>=', $day];
            $whereb[] = ['created_at', '<=',date('Y-m-d H:i:s',strtotime($day)+86400)];

            $data[$key]['day']=$day;
            $data[$key]['channel_name']=$sale['channel_name'];


            $data[$key]['uv_total']=DB::table('sp_extension_statistics')
                ->where('sale_user_id',$sale['sale_user_id'])
                ->where($wherea)
                ->distinct('ip')
                ->count();


            $order_ides=DB::table('sp_order_2')
                ->where('sale_user_id',$sale['sale_user_id'])
                ->orderBy('id','asc')
                ->distinct('uid')
                ->where($wherea)
                ->pluck('id');

            $data[$key]['register_total']=count($order_ides);

//            $data[$key]['register_total']=DB::table('sp_member_2')
//                ->whereIn('id',$uids)
//                ->where($wherea)
//                ->count();

            $data[$key]['register_uv_present']=$data[$key]['register_total']?bcdiv($data[$key]['register_total']*100,$data[$key]['uv_total'],2).'%':'0%';


            $data[$key]['push_order_total']=DB::table('sp_other_order')
                ->whereIn('order_id',$order_ides)
                ->where($whereb)
                ->count();

            $data[$key]['push_uv_present']= $data[$key]['uv_total']?bcdiv($data[$key]['push_order_total']*100, $data[$key]['uv_total'],2).'%':'0%';

            $data[$key]['push_register_present']=$data[$key]['register_total']?bcdiv($data[$key]['push_order_total']*100,$data[$key]['register_total'],2).'%':'0%';

            $data[$key]['push_uv_present']= $data[$key]['uv_total']?bcdiv($data[$key]['push_order_total']*100, $data[$key]['uv_total'],2).'%':'0%';


        }
//    }
        rsort($data);
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$data);
    }



    public function checkData($type='week'){
        $data=[];
        $adminUser = NewAdmin::from('sp_other_admin as admin')
            ->leftjoin('sp_other_auth_group as group','admin.role_id','=','group.id')
            ->select('admin.id','admin.mobile','admin.username','admin.create_time','admin.status','admin.type','admin.user_id','admin.role_id','group.title','group.rules')
            ->orderBy('admin.create_time','desc')
            ->get();
        $adminUser = $adminUser?$adminUser->toArray():[];

        switch($type){
            case'week':
                $date=$this->tradeDays(7);
                $series=[];
                $username=[];
                foreach ($adminUser as $key=>$user){
                    $series['username']=$user['username'];
                    $username[$key][]=$user['username'];
                    foreach ($date as $ra){
                        $series[$key]['passData']=(new OrderLog)->getCount($user['id'],$ra);
                        $series[$key]['cheackData']=(new OrderLog)->getCheckData($user['id'],$ra);

                    }
                }
                break;
            case'month':
                $date=$this->tradeDays(30);
                $series=[];
                $username=[];
                foreach ($adminUser as $key=>$user){
                    $series['username']=$user['username'];
                    $username[$key][]=$user['username'];
                    foreach ($date as $ra){
                        $series[$key]['passData']=(new OrderLog)->getCount($user['id'],$ra);
                        $series[$key]['cheackData']=(new OrderLog)->getCheckData($user['id'],$ra);

                    }
                }
                break;
            case'threeMonth':
                $date=$this->tradeDays(90);
                $series=[];
                $username=[];
                foreach ($adminUser as $key=>$user){
                    $series['username']=$user['username'];
                    $username[$key][]=$user['username'];
                    foreach ($date as $ra){
                        $series[$key]['passData']=(new OrderLog)->getCount($user['id'],$ra);
                        $series[$key]['cheackData']=(new OrderLog)->getCheckData($user['id'],$ra);

                    }
                }
                break;
            case'sixMonth':
                $date=$this->tradeDays(180);
                $series=[];
                $username=[];
                foreach ($adminUser as $key=>$user){
                    $series['username']=$user['username'];
                    $username[$key][]=$user['username'];
                    foreach ($date as $ra){
                        $series[$key]['passData']=(new OrderLog)->getCount($user['id'],$ra);
                        $series[$key]['cheackData']=(new OrderLog)->getCheckData($user['id'],$ra);

                    }
                }
                break;
            case'year':
                $date=$this->tradeDays(365);
                $series=[];
                $username=[];
                foreach ($adminUser as $key=>$user){
                    $series['username']=$user['username'];
                    $username[$key][]=$user['username'];
                    foreach ($date as $ra){
                        $series[$key]['passData']=(new OrderLog)->getCount($user['id'],$ra);
                        $series[$key]['cheackData']=(new OrderLog)->getCheckData($user['id'],$ra);
                    }
                }
                break;
            default:
                $this->tradeDays(7);
        }

        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,compact('date','series','username'));

    }

    //获取最近日期
    public function tradeDays($days=7){
        // 获取当前周几
        $date = [];
        for ($i = 0; $i < $days; $i++) {
            $date[] = date('Y-m-d', strtotime('-' . $i . ' days'));
        }
        return array_reverse($date);
    }


    /**
     * 每日还款表
     * @return void
     */
    public function everyRepaymentData(Request $request){
        if(isset($request['orderCreateTime'])&&$request['orderCreateTime']){
            $times=strtotime(date('Y-m-d',strtotime($request['orderCreateTime'])));
        }else{
            $times=strtotime(date('Y-m-d'));
        }
        $where=[];
        if(isset($request['is_pay'])&&$request['is_pay']&&$request['is_pay']==1){
            $where[] = ['ol.status',1];
        }
        if(isset($request['which_period'])&&$request['which_period']){
            $where[] = ['ol.which_period',$request['which_period']];
        }
        if(isset($request['is_pay'])&&$request['is_pay']&&$request['is_pay']!=1){
            $where[] = ['ol.status','<>',1];
        }
        if(isset($request['riskMobile'])&&$request['riskMobile']){
            $where[] = ['m.mobile', 'like', '%' . $request['riskMobile'] . '%'];
        }
        if(isset($request['shopName'])&&$request['shopName']){
            $where[] = ['s.shopname',  $request['shopName'] ];
        }
        if(isset($request['certName'])&&$request['certName']){
            $where[] = ['m.cert_name', 'like', '%' . $request['certName'] . '%'];
        }
        if(isset($request['orderNextTime'])&&$request['orderNextTime']){
            $times=strtotime($request['orderNextTime']);
            $where[] = ['ol.status','<>',1];
        }
        $orderLista = Order::from('sp_order_2 as o')
            ->leftjoin('sp_shop_user_2 as s', 'o.seller_id', 's.member_id')
            ->leftjoin('sp_member_2 as m', 'o.uid', 'm.id')
            ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
            ->where('ol.begin_time', '>=',$times)
            ->where('ol.begin_time', '<',$times+86400)
            ->where('ol.which_period','<>',1)
            ->where($where)
            ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
            ->orderBy('ol.begin_time', 'asc')
            ->orderBy('o.id', 'desc')
            ->select(
                'o.id', 'o.status', 'o.trade_no', 'o.rent_total', 'o.month_total', 'o.amount', 'o.pay_type', 'o.foregift', 'o.foregift_deduct','o.is_lock'
                , 'o.remark', 'o.realname', 'o.mobile as address_mobile', 'o.address', 'o.sign_status', 'o.guarantee_status', 'o.pact_url', 'o.is_skip', 'o.order_from'
                , 'o.prov', 'o.city', 'o.area', 'o.begin_time', 'o.end_time', 'o.create_time', 'o.rights_auth_no', 'o.check_refuse_reason', 'o.is_abnormal'
                , 'o.is_renewal', 'o.parent_order_no', 'o.emergency_contact', 'o.seller_id', 'o.guarantee_num'
                , 'm.mobile', 'm.cert_name', 'm.cert_no', 'm.idcard_front', 'm.idcard_back', 'm.gender', 's.shopname',
                'ol.id as ol_id', 'ol.begin_time as ol_begin_time', 'ol.end_time as ol_end_time', 'ol.status as ol_status','ol.pay_time','ol.rent','ol.paid_rent','ol.which_period'
            )->get()->toArray();
        $orderList=[];
        foreach ($orderLista as $key=>$ra){
            $ra['ol_begin_time']=date('Y-m-d',$ra['ol_begin_time']);
            $tiime=OrderLease::where('order_id',$ra['id'])->where('status','<>',1)->orderBy('id','asc')->value('begin_time');
            $ra['next_time'] =date('Y-m-d',$tiime);
            if(($ra['ol_begin_time']!=$ra['next_time']&&$request['orderNextTime'])||($ra['ol_begin_time']!=$ra['next_time']&&$request['is_overdue']==1)){
                continue;
            }
            if($ra['ol_begin_time']<=$ra['next_time']&&$request['is_overdue']==2){
                continue;
            }
            $ra['date']=$ra['ol_begin_time'];
            $ra['pay_time']=$ra['pay_time']?date('Y-m-d H:i:s',$ra['pay_time']):'';
            $paid_rent=OrderLeasePartRemark::where('order_lease_id',$ra['ol_id'])->sum('amount');
            if($paid_rent){
                $ra['part_rent']=$paid_rent;
            }else{
                $ra['part_rent']=$ra['paid_rent']>0&&$ra['paid_rent']<$ra['rent']?$ra['paid_rent']:'';
            }
            $ra['is_lock']=$ra['is_lock']?'是':'否';
            $ra['ol_status']=$ra['ol_status']==1?'是':'未还款';
            $ra['orderRemark'] = OrderRemark::getRemark($ra['id']);

            $ra['statusName'] = OrderService::ORDER_STATUS_NAME_LIST[$ra['status']];


            $orderList[]=$ra;
        }
        $total=count($orderList);
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',compact('orderList','total'));

    }

    /**
     * 每日还款数据
     */
    public function everyRepaymentList(Request $request){
        $orderCreateTime = $request->input('orderCreateTime', '');
        $is_overdue = $request->input('is_overdue', '');

        if($orderCreateTime){
            $orderCreateTimea = explode('~', $orderCreateTime);
            $days = new \DatePeriod(
                new \DateTime($orderCreateTimea[0]),
                new \DateInterval('P1D'),
                new \DateTime($orderCreateTimea[1])
            );
        }else{
            $days = new \DatePeriod(
                new \DateTime(date('Y-m-d',strtotime('-1 week'))),
                new \DateInterval('P1D'),
                new \DateTime(date('Y-m-d',time()+86400))
            );
        }

        $data=[];
        if (!empty($request->input('shopName'))) {
            $member_id=ShopUser::where('shopname',$request->input('shopName'))->value('member_id');
        }



        foreach ($days as $key=>$today){
            $day=$today->format('Y-m-d');
            $wherea=[];
            $data[$key]['day']=$today->format('Y-m-d');

            if($is_overdue==1||$is_overdue==2){ //是
                $order_ids=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_OVERDUE_COUNT))
                    ->groupBy('ol.order_id')
                    ->select(
                        DB::raw("SUM(CASE WHEN ol.status = '4' THEN 1 ELSE 0 END) as counta"),'ol.order_id'
                    )
                    ->having('counta','>',0)
                    ->pluck('ol.order_id');
                $is_overdue_order_ids=obToArray($order_ids);
            }


            $wherea[] = ['ol.begin_time', '>=', strtotime($day)];
            $wherea[] = ['ol.begin_time', '<',strtotime($day)+86400];
            $wherea[] = ['ol.which_period','<>',1];

            if($member_id){
                $wherea[] = ['o.seller_id', $member_id];
            }

            $should_back_order=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_OVERDUE_COUNT))
                ->where($wherea)
                ->distinct('ol.order_id');

            if($is_overdue==1){
                $should_back_order->whereNotIn('o.id',$is_overdue_order_ids);
            }
            if($is_overdue==2){
                $should_back_order->whereIn('o.id',$is_overdue_order_ids);
            }
            $data[$key]['should_back_order']= $should_back_order->count();//应还订单

            $have_back_order=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_OVERDUE_COUNT))
                ->where('ol.status',1)
                ->where($wherea)
                ->distinct('ol.order_id');
            if($is_overdue==1){
                $have_back_order->whereNotIn('o.id',$is_overdue_order_ids);
            }
            if($is_overdue==2){
                $have_back_order->whereIn('o.id',$is_overdue_order_ids);
            }
            $success_back_order=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->where('o.status',60)
                ->where('ol.status',0)
                ->where($wherea)
                ->distinct('ol.order_id');
            if($is_overdue==1){
                $success_back_order->whereNotIn('o.id',$is_overdue_order_ids);
            }
            if($is_overdue==2){
                $success_back_order->whereIn('o.id',$is_overdue_order_ids);
            }
            $success_back_order= $success_back_order->count();
            $data[$key]['have_back_order']= $have_back_order->count();//已还订单
            $data[$key]['no_back_order']=bcsub($data[$key]['should_back_order'],$data[$key]['have_back_order']);
            $data[$key]['repayment_rate']=$data[$key]['have_back_order']?bcdiv($data[$key]['have_back_order']*100,$data[$key]['should_back_order'],2).'%':'0%';

            $should_back_amount=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_OVERDUE_COUNT))
                ->where($wherea);

            if($is_overdue==1){
                $should_back_amount->whereNotIn('o.id',$is_overdue_order_ids);
            }
            if($is_overdue==2){
                $should_back_amount->whereIn('o.id',$is_overdue_order_ids);
            }
            $data[$key]['should_back_amount']=  $should_back_amount->sum('ol.rent');//应还金额


            $have_back_amount=Order::from('sp_order_2 as o')
                ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_OVERDUE_COUNT))
                ->where('ol.status',1)
                ->where($wherea);

            if($is_overdue==1){
                $have_back_amount->whereNotIn('o.id',$is_overdue_order_ids);
            }
            if($is_overdue==2){
                $have_back_amount->whereIn('o.id',$is_overdue_order_ids);
            }
            $data[$key]['have_back_amount']=$have_back_amount->sum('ol.paid_rent');//已还金额

            $data[$key]['no_back_amount']=bcsub($data[$key]['should_back_amount'],$data[$key]['have_back_amount']);

            $data[$key]['repayment_amount_rate']=$data[$key]['have_back_amount']?bcdiv($data[$key]['have_back_amount']*100,$data[$key]['should_back_amount'],2).'%':'0%';

            $data[$key]['overdue_amount_rate']=$data[$key]['have_back_amount']?bcsub(100,bcdiv($data[$key]['have_back_amount']*100,$data[$key]['should_back_amount'],2)).'%':'0%';

        }
        rsort($data);
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$data);

    }

    public function dailyRiskControlData(Request $request)
    {
        $orderCreateTime = $request->input('orderCreateTime', '');
        $sale_user_id= $request->input('sale_user_id', '');


        if($orderCreateTime){
            $orderCreateTimea = explode('~', $orderCreateTime);
            $days = new \DatePeriod(
                new \DateTime($orderCreateTimea[0]),
                new \DateInterval('P1D'),
                new \DateTime($orderCreateTimea[1])
            );
        }else{
            $days = new \DatePeriod(
                new \DateTime(date('Y-m-d',strtotime('-1 week'))),
                new \DateInterval('P1D'),
                new \DateTime(date('Y-m-d',time()+86400))
            );
        }

        if (!empty($request->input('shopName'))) {
            $member_id=ShopUser::where('shopname',$request->input('shopName'))->value('member_id');
        }

        $data=[];
        foreach ($days as $key=>$today){
            $day=$today->format('Y-m-d');
            $wherea=[];
            $data[$key]['day']=$today->format('Y-m-d');


            $wherea[] = ['mqr.create_time', '>=', strtotime($day)];
            $wherea[] = ['mqr.create_time', '<=',strtotime($day)+86399];
            if($member_id){
                $wherea[] = ['o.seller_id', $member_id];
            }
            //总订单统计
            $RiskCredit=MemberQnRisk::from('sp_member_qnyp_risk as mqr')
                ->leftJoin('sp_member_2 as m','mqr.uid','m.id')
                ->leftJoin('sp_order_2 as o','o.uid','mqr.uid')
                ->distinct('o.uid')
                ->where($wherea);
            if($sale_user_id&&is_array($sale_user_id)){
                if(count($sale_user_id)==1&&$sale_user_id[0]==1){
                    $RiskCredit->whereNull('o.sale_user_id');
                }else{
                    $RiskCredit->whereIn('o.sale_user_id',$sale_user_id);
                }
            }
            $data[$key]['total_risk']=$RiskCredit->count();

            $wherea[] = ['mqr.create_time', '>=', strtotime($day)];
            $wherea[] = ['mqr.create_time', '<=',strtotime($day)+86399];
            if($member_id){
                $wherea[] = ['o.seller_id', $member_id];
            }
            //总订单统计
            $RiskCredit=OrderDlzwRisk::from('sp_order_dlzw_risk as mqr')
                ->leftJoin('sp_member_2 as m','mqr.uid','m.id')
                ->leftJoin('sp_order_2 as o','o.uid','mqr.uid')
                ->distinct('o.uid')
                ->where($wherea);
            if($sale_user_id&&is_array($sale_user_id)){
                if(count($sale_user_id)==1&&$sale_user_id[0]==1){
                    $RiskCredit->whereNull('o.sale_user_id');
                }else{
                    $RiskCredit->whereIn('o.sale_user_id',$sale_user_id);
                }
            }
            $data[$key]['total_risk_dlz']=$RiskCredit->count();


            $total_pass_risk=OrderDlzwRisk::from('sp_order_dlzw_risk as mqr')
                ->leftJoin('sp_member_2 as m','mqr.uid','m.id')
                ->leftJoin('sp_order_2 as o','o.uid','mqr.uid')
                ->whereIn('risk_level',['A','B','C','D'])
                ->distinct('o.uid')
                ->where($wherea);

            if($sale_user_id&&is_array($sale_user_id)){
                if(count($sale_user_id)==1&&$sale_user_id[0]==1){
                    $total_pass_risk->whereNull('o.sale_user_id');
                }else{
                    $total_pass_risk->whereIn('o.sale_user_id',$sale_user_id);
                }
            }

            $data[$key]['total_pass_risk_dlz']=$total_pass_risk->count();


            $data[$key]['total_pass_risk_rate_dlz']=$data[$key]['total_pass_risk_dlz']?bcdiv($data[$key]['total_pass_risk_dlz']*100,$data[$key]['total_risk_dlz'],2).'%':'0%';


            //免押订单统计
            $total_foregift_risk=MemberQnRisk::from('sp_order_dlzw_risk as mqr')
                ->leftJoin('sp_member_2 as m','mqr.uid','m.id')
                ->leftJoin('sp_order_2 as o','o.uid','mqr.uid')
                ->distinct('o.uid')
                ->where($wherea)
                ->where('o.foregift_deduct','>',0);

            if($sale_user_id&&is_array($sale_user_id)){
                if(count($sale_user_id)==1&&$sale_user_id[0]==1){
                    $total_foregift_risk->whereNull('o.sale_user_id');
                }else{
                    $total_foregift_risk->whereIn('o.sale_user_id',$sale_user_id);
                }
            }
            $data[$key]['total_foregift_risk_dlz']=$total_foregift_risk->count();


            $data[$key]['total_foregift_risk_rate_dlz']=$data[$key]['total_foregift_risk_dlz']?bcdiv($data[$key]['total_foregift_risk_dlz']*100,$data[$key]['total_risk_dlz'],2).'%':'0%';

            $total_risk_a=MemberQnRisk::from('sp_order_dlzw_risk as mqr')
                ->leftJoin('sp_member_2 as m','mqr.uid','m.id')
                ->leftJoin('sp_order_2 as o','o.uid','mqr.uid')
                ->distinct('o.uid')
                ->where($wherea)
                ->where('mqr.risk_level','A');

            if($sale_user_id&&is_array($sale_user_id)){
                if(count($sale_user_id)==1&&$sale_user_id[0]==1){
                    $total_risk_a->whereNull('o.sale_user_id');
                }else{
                    $total_risk_a->whereIn('o.sale_user_id',$sale_user_id);
                }
            }
            $data[$key]['total_risk_a_dlz']=$total_risk_a->count();


            $data[$key]['total_risk_a_rate_dlz']=($data[$key]['total_risk_a_dlz']?bcdiv($data[$key]['total_risk_a_dlz']*100,$data[$key]['total_risk_dlz'],2).'%':'0%').'（'.$data[$key]['total_risk_a_dlz'].')';

            $total_risk_b=MemberQnRisk::from('sp_order_dlzw_risk as mqr')
                ->leftJoin('sp_member_2 as m','mqr.uid','m.id')
                ->leftJoin('sp_order_2 as o','o.uid','mqr.uid')
                ->distinct('o.uid')
                ->where($wherea)
                ->where('mqr.risk_level','B');

            if($sale_user_id&&is_array($sale_user_id)){
                if(count($sale_user_id)==1&&$sale_user_id[0]==1){
                    $total_risk_b->whereNull('o.sale_user_id');
                }else{
                    $total_risk_b->whereIn('o.sale_user_id',$sale_user_id);
                }
            }

            $data[$key]['total_risk_b_dlz']=$total_risk_b->count();

            $data[$key]['total_risk_b_rate_dlz']=($data[$key]['total_risk_b_dlz']?bcdiv($data[$key]['total_risk_b_dlz']*100,$data[$key]['total_risk_dlz'],2).'%':'0%').'（'.$data[$key]['total_risk_b_dlz'].')';

            $total_risk_c=MemberQnRisk::from('sp_order_dlzw_risk as mqr')
                ->leftJoin('sp_member_2 as m','mqr.uid','m.id')
                ->leftJoin('sp_order_2 as o','o.uid','mqr.uid')
                ->distinct('o.uid')
                ->where($wherea)
                ->where('mqr.risk_level','C');
            if($sale_user_id&&is_array($sale_user_id)){
                if(count($sale_user_id)==1&&$sale_user_id[0]==1){
                    $total_risk_c->whereNull('o.sale_user_id');
                }else{
                    $total_risk_c->whereIn('o.sale_user_id',$sale_user_id);
                }
            }
            $data[$key]['total_risk_c_dlz']=$total_risk_c->count();


            $data[$key]['total_risk_c_rate_dlz']=($data[$key]['total_risk_c_dlz']?bcdiv($data[$key]['total_risk_c_dlz']*100,$data[$key]['total_risk_dlz'],2).'%':'0%').'（'.$data[$key]['total_risk_c_dlz'].')';

            $total_risk_d=MemberQnRisk::from('sp_order_dlzw_risk as mqr')
                ->leftJoin('sp_member_2 as m','mqr.uid','m.id')
                ->leftJoin('sp_order_2 as o','o.uid','mqr.uid')
                ->distinct('o.uid')
                ->where($wherea)
                ->where('mqr.risk_level','D');
            if($sale_user_id&&is_array($sale_user_id)){
                if(count($sale_user_id)==1&&$sale_user_id[0]==1){
                    $total_risk_d->whereNull('o.sale_user_id');
                }else{
                    $total_risk_d->whereIn('o.sale_user_id',$sale_user_id);
                }
            }
            $data[$key]['total_risk_d_dlz']=$total_risk_d->count();

            $data[$key]['total_risk_d_rate_dlz']=($data[$key]['total_risk_d_dlz']?bcdiv($data[$key]['total_risk_d_dlz']*100,$data[$key]['total_risk_dlz'],2).'%':'0%').'（'.$data[$key]['total_risk_d_dlz'].')';

            $total_risk_e=MemberQnRisk::from('sp_order_dlzw_risk as mqr')
                ->leftJoin('sp_member_2 as m','mqr.uid','m.id')
                ->leftJoin('sp_order_2 as o','o.uid','mqr.uid')
                ->distinct('o.uid')
                ->where($wherea)
                ->where('mqr.risk_level','E');
            if($sale_user_id&&is_array($sale_user_id)){
                if(count($sale_user_id)==1&&$sale_user_id[0]==1){
                    $total_risk_e->whereNull('o.sale_user_id');
                }else{
                    $total_risk_e->whereIn('o.sale_user_id',$sale_user_id);
                }
            }
            $data[$key]['total_risk_e_dlz']=$total_risk_e->count();

            $data[$key]['total_risk_e_rate_dlz']=($data[$key]['total_risk_e_dlz']?bcdiv($data[$key]['total_risk_e_dlz']*100,$data[$key]['total_risk_dlz'],2).'%':'0%').'（'.$data[$key]['total_risk_e_dlz'].')';

            $total_risk_f=MemberQnRisk::from('sp_order_dlzw_risk as mqr')
                ->leftJoin('sp_member_2 as m','mqr.uid','m.id')
                ->leftJoin('sp_order_2 as o','o.uid','mqr.uid')
                ->distinct('o.uid')
                ->where($wherea)
                ->where('mqr.risk_level','F');
            if($sale_user_id&&is_array($sale_user_id)){
                if(count($sale_user_id)==1&&$sale_user_id[0]==1){
                    $total_risk_f->whereNull('o.sale_user_id');
                }else{
                    $total_risk_f->whereIn('o.sale_user_id',$sale_user_id);
                }
            }
            $data[$key]['total_risk_f_dlz']=$total_risk_f->count();

            $data[$key]['total_risk_f_rate_dlz']=($data[$key]['total_risk_f_dlz']?bcdiv($data[$key]['total_risk_f_dlz']*100,$data[$key]['total_risk_dlz'],2).'%':'0%').'（'.$data[$key]['total_risk_f_dlz'].')';

        }
        rsort($data);
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$data);

    }


    public function orderCollectorCount(Request $request)
    {
        $orderCreateTime = $request->input('orderCreateTime', '');
        $admin_id= $request->input('admin_id', '');
        $is_overdue = $request->input('is_overdue', '');

        if($this->users['is_collector']==2){
            $admin_id=$this->users['id'];
        }

        if($orderCreateTime){
            $orderCreateTimea = explode('~', $orderCreateTime);
            $days = new \DatePeriod(
                new \DateTime($orderCreateTimea[0]),
                new \DateInterval('P1D'),
                new \DateTime($orderCreateTimea[1])
            );
        }else{
            $days = new \DatePeriod(
                new \DateTime(date('Y-m-d',strtotime('-1 week'))),
                new \DateInterval('P1D'),
                new \DateTime(date('Y-m-d',time()+86400))
            );
        }


        $data=[];
        foreach ($days as $key=>$today){
            $day=$today->format('Y-m-d');
            $wherea=[];
            $data[$key]['day']=$today->format('Y-m-d');

            $wherea[] = ['mqr.create_time', '>=', $day];
            $wherea[] = ['mqr.create_time', '<=',$today->format('Y-m-d 23:59:59')];
            if($admin_id){
                $wherea[] = ['mqr.admin_id',$admin_id];
            }
            $order_ids=[];
            if($is_overdue==1||$is_overdue==2){
                $order_ids=OrderCollector::query()->from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->leftJoin('sp_order_collector as mqr','mqr.order_id','o.id')
                    ->where($wherea)
                    ->groupBy('ol.order_id')
                    ->select(
                        DB::raw("SUM(CASE WHEN ol.status = '4' THEN 1 ELSE 0 END) as counta"),'ol.order_id'
                    )
                    ->having('counta','>',0)
                    ->pluck('ol.order_id');
                $order_ids=obToArray($order_ids);
            }

            $query = OrderCollector::query()->from('sp_order_collector as mqr')
                ->leftJoin('sp_order_lease_2 as ol', 'ol.id', 'mqr.order_lease_id')
                ->where($wherea);

            $queryAmount = OrderCollector::query()->from('sp_order_collector as mqr')
                ->leftJoin('sp_order_lease_2 as ol', 'ol.id', 'mqr.order_lease_id')
                ->leftJoin('sp_order_buyout_audit as oba','oba.order_id','mqr.order_id')
                ->where($wherea);
            if ($is_overdue == 1 ) {
                $query->whereNotIn('mqr.order_id', $order_ids);
            } elseif ($is_overdue == 2) {
                $query->whereIn('mqr.order_id', $order_ids);
            }
            $cloneQuery = clone $query;
            //订单应还
            $data[$key]['total_order'] = $cloneQuery->count();
            $cloneQuery = clone $query;
            $data[$key]['total_amount_sum'] = $cloneQuery->sum('ol.rent');
            // 订单0应还
            $cloneQuery = clone $query;

            $data[$key]['t0_order_total'] = $cloneQuery
                ->where('ol.overdue_days', 0)
                ->where('mqr.status', 3)
                ->count();

            $cloneAmountQuery = clone $queryAmount;

            $amounta=$cloneAmountQuery
                ->where('ol.overdue_days',0)
                ->groupBy('mqr.order_id')
                ->where('oba.check_status',1)
                ->sum('oba.amount');

            $cloneQuery = clone $query;

            $data[$key]['t0_rent_total'] = $cloneQuery
                    ->where('ol.overdue_days', 0)
                    ->where('mqr.status', 3)
                    ->sum('ol.paid_rent') + $amounta;
            $cloneQuery = clone $query;
            $data[$key]['t1_order_total'] = $cloneQuery
                ->where('ol.overdue_days', 1)
                ->where('mqr.status', 3)
                ->count();

            $cloneAmountQuery = clone $queryAmount;
            $amounta=$cloneAmountQuery
                ->where('ol.overdue_days',1)
                ->groupBy('mqr.order_id')
                ->where('oba.check_status',1)
                ->sum('oba.amount');
            $cloneQuery = clone $query;

            $data[$key]['t1_rent_total'] = $cloneQuery
                    ->where('ol.overdue_days', 1)
                    ->where('mqr.status', 3)
                    ->sum('ol.paid_rent') + $amounta;
            $cloneQuery = clone $query;

            $data[$key]['t2_order_total'] = $cloneQuery
                ->where('ol.overdue_days', 2)
                ->where('mqr.status', 3)
                ->count();

            $cloneAmountQuery = clone $queryAmount;
            $amounta=$cloneAmountQuery
                ->where('ol.overdue_days',2)
                ->groupBy('mqr.order_id')
                ->where('oba.check_status',1)
                ->sum('oba.amount');

            $cloneQuery = clone $query;
            $data[$key]['t2_rent_total'] = $cloneQuery
                    ->where('ol.overdue_days', 2)
                    ->where('mqr.status', 3)
                    ->sum('ol.paid_rent') + $amounta;

            $cloneQuery = clone $query;
            $data[$key]['t3_order_total'] = $cloneQuery
                ->where('ol.overdue_days', 3)
                ->where('mqr.status', 3)
                ->count();

            $cloneAmountQuery = clone $queryAmount;
            $amounta=$cloneAmountQuery
                ->where('ol.overdue_days',3)
                ->groupBy('mqr.order_id')
                ->where('oba.check_status',1)
                ->sum('oba.amount');
            $cloneQuery = clone $query;

            $data[$key]['t3_rent_total'] = $cloneQuery
                    ->where('ol.overdue_days', 3)
                    ->where('mqr.status', 3)
                    ->sum('ol.paid_rent') + $amounta;

            $cloneQuery = clone $query;
            $data[$key]['t4_order_total'] = $cloneQuery
                ->where('ol.overdue_days', 4)
                ->where('mqr.status', 3)
                ->count();

            $cloneAmountQuery = clone $queryAmount;
            $amounta=$cloneAmountQuery
                ->where('ol.overdue_days',4)
                ->groupBy('mqr.order_id')
                ->where('oba.check_status',1)
                ->sum('oba.amount');

            $cloneQuery = clone $query;
            $data[$key]['t4_rent_total'] = $cloneQuery
                    ->where('ol.overdue_days', 4)
                    ->where('mqr.status', 3)
                    ->sum('ol.paid_rent') + $amounta;

            $cloneQuery = clone $query;
            $data[$key]['t5_order_total'] = $cloneQuery
                ->where('ol.overdue_days', 5)
                ->where('mqr.status', 3)
                ->count();

            $cloneAmountQuery = clone $queryAmount;
            $amounta=$cloneAmountQuery
                ->where('ol.overdue_days',5)
                ->groupBy('mqr.order_id')
                ->where('oba.check_status',1)
                ->sum('oba.amount');

            $cloneQuery = clone $query;
            $data[$key]['t5_rent_total'] = $cloneQuery
                    ->where('ol.overdue_days', 5)
                    ->where('mqr.status', 3)
                    ->sum('ol.paid_rent') + $amounta;

            $cloneQuery = clone $query;
            $data[$key]['t6_order_total'] = $cloneQuery
                ->where('ol.overdue_days', 6)
                ->where('mqr.status', 3)
                ->count();

            $cloneAmountQuery = clone $queryAmount;
            $amounta=$cloneAmountQuery
                ->where('ol.overdue_days',6)
                ->groupBy('mqr.order_id')
                ->where('oba.check_status',1)
                ->sum('oba.amount');

            $cloneQuery = clone $query;
            $data[$key]['t6_rent_total'] = $cloneQuery
                    ->where('ol.overdue_days', 6)
                    ->where('mqr.status', 3)
                    ->sum('ol.paid_rent') + $amounta;

            $cloneQuery = clone $query;
            $data[$key]['t7_order_total'] = $cloneQuery
                ->where('ol.overdue_days', 7)
                ->where('mqr.status', 3)
                ->count();

            $cloneAmountQuery = clone $queryAmount;
            $amounta=$cloneAmountQuery
                ->where('ol.overdue_days',7)
                ->groupBy('mqr.order_id')
                ->where('oba.check_status',1)
                ->sum('oba.amount');

            $cloneQuery = clone $query;
            $data[$key]['t7_rent_total'] = $cloneQuery
                    ->where('ol.overdue_days', 7)
                    ->where('mqr.status', 3)
                    ->sum('ol.paid_rent') + $amounta;

            $cloneQuery = clone $query;
            $data[$key]['total_amount_pay_sum'] = $cloneQuery
                ->where('ol.overdue_days', '<=', 60)
                ->where('mqr.status', 3)
                ->sum('ol.paid_rent');
            $cloneQuery = clone $query;
            $data[$key]['pay_order_total'] = $cloneQuery
                ->where('ol.overdue_days', '<=', 60)
                ->where('mqr.status', 3)
                ->count();

            $data[$key]['total_amount_rate']=$data[$key]['total_amount_pay_sum']?bcdiv($data[$key]['total_amount_pay_sum']*100,$data[$key]['total_amount_sum'],2).'%':'0%';
            $data[$key]['total_order_rate']=$data[$key]['pay_order_total']?bcdiv($data[$key]['pay_order_total']*100,$data[$key]['total_order'],2).'%':'0%';


        }
        rsort($data);
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$data);

    }

    public function orderCollectorStatistics(Request $request)
    {
        $orderCreateTime = $request->input('orderCreateTime', '');
        $admin_id= $request->input('admin_id', '');

        if($this->users['is_collector']==2){
            $admin_id=$this->users['id'];
        }

        if($orderCreateTime){
            $orderCreateTimea = explode('~', $orderCreateTime);
            $days = new \DatePeriod(
                new \DateTime($orderCreateTimea[0]),
                new \DateInterval('P1D'),
                new \DateTime($orderCreateTimea[1])
            );
        }else{
            $days = new \DatePeriod(
                new \DateTime(date('Y-m-d',strtotime('-1 week'))),
                new \DateInterval('P1D'),
                new \DateTime(date('Y-m-d',time()+86400))
            );
        }

        $data=[];
        foreach ($days as $key=>$today){
            $day=$today->format('Y-m-d');
            $wherea=[];
            $data[$key]['day']=$today->format('Y-m-d');


            $wherea[] = ['mqr.create_time', '>=', $day];
            $wherea[] = ['mqr.create_time', '<=',$today->format('Y-m-d 23:59:59')];
            if($admin_id){
                $wherea[] = ['mqr.admin_id',$admin_id];
            }
            //订单应还
            $data[$key]['total_order']=OrderCollector::from('sp_order_collector as mqr')
                ->where($wherea)->count();

            $data[$key]['total_amount_sum']=OrderCollector::from('sp_order_collector as mqr')
                ->leftJoin('sp_order_lease_2 as ol','ol.id','mqr.order_lease_id')
                ->where($wherea)
                ->sum('ol.rent');
            //订单0应还
            $data[$key]['t0_order_total']=OrderCollector::from('sp_order_collector as mqr')
                ->leftJoin('sp_order_lease_2 as ol','ol.id','mqr.order_lease_id')
                ->where('ol.overdue_days','<=',7)
                ->where($wherea)
                ->where('mqr.status',3)->count();


            $data[$key]['t0_rent_total']=OrderCollector::from('sp_order_collector as mqr')
                ->leftJoin('sp_order_lease_2 as ol','ol.id','mqr.order_lease_id')
                ->where($wherea)
                ->where('ol.overdue_days','<=',7)
                ->where('mqr.status',3)
                ->sum('ol.paid_rent');

            $data[$key]['t1_order_total']=OrderCollector::from('sp_order_collector as mqr')
                ->leftJoin('sp_order_lease_2 as ol','ol.id','mqr.order_lease_id')
                ->where('ol.overdue_days','>',7)
                ->where($wherea)
                ->where('mqr.status',3)->count();

            $data[$key]['t1_rent_total']=OrderCollector::from('sp_order_collector as mqr')
                ->leftJoin('sp_order_lease_2 as ol','ol.id','mqr.order_lease_id')
                ->where($wherea)
                ->where('ol.overdue_days','>',7)
                ->where('mqr.status',3)
                ->sum('ol.paid_rent');

            $data[$key]['total_amount_pay_sum']=OrderCollector::from('sp_order_collector as mqr')
                ->leftJoin('sp_order_lease_2 as ol','ol.id','mqr.order_lease_id')
                ->where($wherea)
                ->where('mqr.status',3)
                ->sum('ol.paid_rent');

            $data[$key]['pay_order_total']=OrderCollector::from('sp_order_collector as mqr')
                ->leftJoin('sp_order_lease_2 as ol','ol.id','mqr.order_lease_id')
                ->where($wherea)
                ->where('mqr.status',3)->count();

            $data[$key]['total_amount_rate']=$data[$key]['t0_rent_total']?bcdiv($data[$key]['t0_rent_total']*100,$data[$key]['total_amount_sum'],2).'%':'0%';
            $data[$key]['total_order_rate']=$data[$key]['t0_order_total']?bcdiv($data[$key]['t0_order_total']*100,$data[$key]['total_order'],2).'%':'0%';

            $data[$key]['t1_total_amount_rate']=$data[$key]['t1_rent_total']?bcdiv($data[$key]['t1_rent_total']*100,$data[$key]['total_amount_sum'],2).'%':'0%';
            $data[$key]['t1_total_order_rate']=$data[$key]['t1_order_total']?bcdiv($data[$key]['t1_order_total']*100,$data[$key]['total_order'],2).'%':'0%';


        }
        rsort($data);
        return ReturnMsg(CommonService::HTTP_CODE_SUCCESS,'获取成功',$data);

    }


    /**
     * 列表导出
     * @param Request $request
     * @return false|string
     */
    public function channelAnalysisExport(Request $request){
        $orderCreateTime = $request->input('orderCreateTime', '');
        $pageSize = $request->input('pageSize',10);
        $page = $request->input('page',1);
        $sale_user_id= $request->input('userId','');
        $risk_level= $request->input('risk_level','');

        if($sale_user_id&&!is_array($sale_user_id)){
            $where= [
                ['sale_user_id',$sale_user_id],
            ];
        }


        if($sale_user_id&&is_array($sale_user_id)){
            $saleUser=SaleUser::where('channel_name','<>','')->whereIn('sale_user_id',$sale_user_id)->select('sale_user_id','channel_name')->get();
        }else{
            $saleUser=SaleUser::where('channel_name','<>','')->where($where)->select('sale_user_id','channel_name')->get();
        }

        $saleUser = $saleUser?$saleUser->toArray():[];

        if ($orderCreateTime) {
            $orderCreateTimea = explode('~', $orderCreateTime);
            $wherea[] = ['o.create_time', '>=', strtotime($orderCreateTimea[0])];
            $wherea[] = ['o.create_time', '<=',strtotime($orderCreateTimea[1])];
        }else{
            $orderCreateTime=date('Y-m-d',strtotime('-1 month')).'~'.date('Y-m-d 23:59:59',time());
            $orderCreateTimea = explode('~', $orderCreateTime);
            $wherea[] = ['o.create_time', '>=', strtotime($orderCreateTimea[0])];
            $wherea[] = ['o.create_time', '<=',strtotime($orderCreateTimea[1])];
        }

        $data=[];

        foreach ($saleUser as &$user){

            $user['day']=date('Y-m-d',strtotime($orderCreateTimea[0])).'~'.date('Y-m-d',strtotime($orderCreateTimea[1]));
            $user['channel_namea']=$user['channel_name'];
            if($risk_level){
                foreach ($risk_level as $k=>$v){
                    $uids=Order::query()->from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->leftjoin('sp_member_qnyp_risk as qr', 'qr.uid', 'o.uid')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where($wherea)
                        ->where('qr.risk_level',$v)->pluck('o.uid');

                    $uids=obToArray($uids);

                    $user['risk_level']=$v;


                    $user['total_order']=Order::from('sp_order_2 as o')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.uid',$uids)
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where($wherea)
                        ->count();

                    $user['total_pay_order']=Order::from('sp_order_2 as o')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',['30','60'])
                        ->where($wherea)
                        ->count();

                    $user['total_overdueing_order']=Order::from('sp_order_2 as o')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('o.status',39)
                        ->where($wherea)
                        ->count();

                    $user['total_backing_order']=Order::from('sp_order_2 as o')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('o.status',38)
                        ->where($wherea)
                        ->count();
                    $user['total_overdue_order']=bcadd($user['total_backing_order'],$user['total_overdueing_order']);



                    $user['order_back_rate']=bcdiv($user['total_pay_order']*100,$user['total_order'],2).'%';
                    $user['order_overdue_rate']=bcsub(100,bcdiv($user['total_pay_order']*100,$user['total_order'],2),2).'%';

                    $user['first_overdue_order']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where('ol.status',4)
                        ->where('ol.which_period',2)
                        ->where($wherea)
                        ->count();
                    $user['first_overdue_rate']=bcdiv($user['first_overdue_order']*100,$user['total_order'],2).'%';


                    $user['total_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('ol.begin_time','<=',time())
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where($wherea)
                        ->sum('ol.rent');

                    $amount=Order::from('sp_order_2 as o')
                        ->leftJoin('sp_order_buyout_audit as oba','oba.order_id','o.id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.uid',$uids)
                        ->where($wherea)
                        ->where('oba.check_status',1)
                        ->sum('oba.amount');

                    $total_pay_amount=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('ol.begin_time','<=',time())
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->whereIn('o.uid',$uids)
                        ->where('ol.status',1)
                        ->where($wherea)
                        ->sum('ol.rent');
                    $user['total_pay_amount']=bcadd($total_pay_amount,$amount,2);

                    $user['remaining_amount']=bcsub($user['total_amount'],$user['total_pay_amount'],2);

                    $user['total_backing_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('o.status',38)
                        ->where('ol.status',4)
                        ->where($wherea)
                        ->sum('ol.rent');


                    $user['total_overdueing_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->whereIn('o.uid',$uids)
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('o.status',39)
                        ->where('ol.status',4)
                        ->where($wherea)
                        ->sum('ol.rent');

                    $user['total_overdue_amount']=bcadd($user['total_backing_amount'],$user['total_overdueing_amount'],2);

                    $user['amount_back_rate']=bcdiv($user['total_pay_amount']*100,$user['total_amount'],2).'%';

                    $user['first_overdue_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->whereIn('o.uid',$uids)
                        ->where('ol.status',4)
                        ->where('ol.which_period',2)
                        ->where($wherea)
                        ->sum('ol.rent');

                    $user['first_overdue_amount_rate']=bcdiv($user['first_overdue_amount']*100,$user['total_amount'],2).'%';


                    $last_un_pay_amount=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->where('ol.begin_time','>',time())
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_BUYOUT_STATUS))
                        ->where('ol.status',0)
                        ->whereIn('o.uid',$uids)
                        ->where($wherea)
                        ->sum('ol.rent');

                    $user['last_un_pay_amount']=$last_un_pay_amount;

                    $user['expect_amount']=bcadd($user['total_pay_amount'],$last_un_pay_amount,2);



                    $user['total_pay_all_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->whereIn('o.uid',$uids)
                        ->where($wherea)
                        ->sum('ol.rent');



                    $user['remainder_week_not_due']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where('ol.begin_time','<=',time())
                        ->where('ol.begin_time','>=',strtotime('-7 days'))
                        ->where($wherea)
                        ->whereIn('o.uid',$uids)
                        ->sum('ol.rent');

                    $user['remainder_week_back_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where('ol.begin_time','<=',time())
                        ->where('ol.begin_time','>=',strtotime('-7 days'))
                        ->whereIn('ol.status',[0,1])
                        ->where($wherea)
                        ->whereIn('o.uid',$uids)
                        ->sum('ol.rent');

                    $user['remainder_not_due']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where('ol.begin_time','<=',time())
                        ->where('ol.begin_time','>=',strtotime('-1 month'))
                        ->where($wherea)
                        ->whereIn('o.uid',$uids)
                        ->sum('ol.rent');

                    $user['remainder_back_amount']=Order::from('sp_order_2 as o')
                        ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->where('ol.begin_time','<=',time())
                        ->where('ol.begin_time','>=',strtotime('-1 month'))
                        ->whereIn('ol.status',[0,1])
                        ->whereIn('o.uid',$uids)
                        ->where($wherea)
                        ->sum('ol.rent');

                    $user['acquisition_price']=Order::query()->from('sp_order_2 as o')
                        ->leftjoin('sp_order_goods_2 as og', 'og.order_id', 'o.id')
                        ->where('o.sale_user_id',$user['sale_user_id'])
                        ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                        ->whereIn('o.uid',$uids)
                        ->where($wherea)
                        ->sum('og.acquisition_price');

                    $user['principal_recovery']=bcdiv($user['total_pay_amount']*100,$user['acquisition_price'],2).'%';

                    $user['fu_principal_recovery']=bcdiv($user['expect_amount']*100,$user['acquisition_price'],2).'%';

                    $user['remainder_week_back_rate']=bcdiv($user['remainder_week_back_amount']*100,$user['remainder_week_not_due'],2).'%';

                    $user['remainder_back_rate']=bcdiv($user['remainder_back_amount']*100,$user['remainder_not_due'],2).'%';

                    $data[]=$user;
                }

            }else{
                $riskLevel=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->leftjoin('sp_member_qnyp_risk as qr', 'qr.uid', 'o.uid')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where($wherea)
                    ->distinct('ol.order_id')
                    ->select(
                        DB::raw("SUM(CASE WHEN qr.risk_level = 'A' THEN 1 ELSE 0 END) as count_A"),
                        DB::raw("SUM(CASE WHEN qr.risk_level = 'B' THEN 1 ELSE 0 END) as count_B"),
                        DB::raw("SUM(CASE WHEN qr.risk_level = 'C' THEN 1 ELSE 0 END) as count_C"),
                        DB::raw("SUM(CASE WHEN qr.risk_level = 'D' THEN 1 ELSE 0 END) as count_D")
                    )
                    ->first();

                if (empty($riskLevel['count_A'])&&empty($riskLevel['count_B'])&&empty($riskLevel['count_C'])&&empty($riskLevel['count_D'])){
                    $user['risk_level']='A:0;B:0;C:0;D:0';
                }else{
                    $user['risk_level']='A:'.$riskLevel['count_A'].'; B:'.$riskLevel['count_B'].'; C:'.$riskLevel['count_C'].'; D:'.$riskLevel['count_D'];
                }

                $user['total_order']=Order::from('sp_order_2 as o')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where($wherea)
                    ->count();

                $user['total_pay_order']=Order::from('sp_order_2 as o')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',['30','60'])
                    ->where($wherea)
                    ->count();

                $user['total_overdueing_order']=Order::from('sp_order_2 as o')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('o.status',39)
                    ->where($wherea)
                    ->count();

                $user['total_backing_order']=Order::from('sp_order_2 as o')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('o.status',38)
                    ->where($wherea)
                    ->count();
                $user['total_overdue_order']=bcadd($user['total_backing_order'],$user['total_overdueing_order']);



                $user['order_back_rate']=bcdiv($user['total_pay_order']*100,$user['total_order'],2).'%';
                $user['order_overdue_rate']=bcsub(100,bcdiv($user['total_pay_order']*100,$user['total_order'],2),2).'%';

                $user['first_overdue_order']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.status',4)
                    ->where('ol.which_period',2)
                    ->where($wherea)
                    ->count();
                $user['first_overdue_rate']=bcdiv($user['first_overdue_order']*100,$user['total_order'],2).'%';


                $user['total_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('ol.begin_time','<=',time())
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where($wherea)
                    ->sum('ol.rent');

                $amount=Order::from('sp_order_2 as o')
                    ->leftJoin('sp_order_buyout_audit as oba','oba.order_id','o.id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where($wherea)
                    ->where('oba.check_status',1)
                    ->sum('oba.amount');

                $total_pay_amount=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('ol.begin_time','<=',time())
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.status',1)
                    ->where($wherea)
                    ->sum('ol.rent');
                $user['total_pay_amount']=bcadd($total_pay_amount,$amount,2);

                $user['remaining_amount']=bcsub($user['total_amount'],$user['total_pay_amount'],2);

                $user['total_backing_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('o.status',38)
                    ->where('ol.status',4)
                    ->where($wherea)
                    ->sum('ol.rent');


                $user['total_overdueing_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('o.status',39)
                    ->where('ol.status',4)
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['total_overdue_amount']=bcadd($user['total_backing_amount'],$user['total_overdueing_amount'],2);

                $user['amount_back_rate']=bcdiv($user['total_pay_amount']*100,$user['total_amount'],2).'%';

                $user['first_overdue_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.status',4)
                    ->where('ol.which_period',2)
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['first_overdue_amount_rate']=bcdiv($user['first_overdue_amount']*100,$user['total_amount'],2).'%';


                $last_un_pay_amount=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->where('ol.begin_time','>',time())
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_BUYOUT_STATUS))
                    ->where('ol.status',0)
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['last_un_pay_amount']=$last_un_pay_amount;

                $user['expect_amount']=bcadd($user['total_pay_amount'],$last_un_pay_amount,2);



                $user['total_pay_all_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where($wherea)
                    ->sum('ol.rent');



                $user['remainder_week_not_due']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.begin_time','<=',time())
                    ->where('ol.begin_time','>=',strtotime('-7 days'))
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['remainder_week_back_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.begin_time','<=',time())
                    ->where('ol.begin_time','>=',strtotime('-7 days'))
                    ->whereIn('ol.status',[0,1])
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['remainder_not_due']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.begin_time','<=',time())
                    ->where('ol.begin_time','>=',strtotime('-1 month'))
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['remainder_back_amount']=Order::from('sp_order_2 as o')
                    ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where('ol.begin_time','<=',time())
                    ->where('ol.begin_time','>=',strtotime('-1 month'))
                    ->whereIn('ol.status',[0,1])
                    ->where($wherea)
                    ->sum('ol.rent');

                $user['acquisition_price']=Order::query()->from('sp_order_2 as o')
                    ->leftjoin('sp_order_goods_2 as og', 'og.order_id', 'o.id')
                    ->where('o.sale_user_id',$user['sale_user_id'])
                    ->whereIn('o.status',OrderService::getOrderStatus(OrderService::ORDER_SETTLE_STATUS))
                    ->where($wherea)
                    ->sum('og.acquisition_price');

                $user['principal_recovery']=bcdiv($user['total_pay_amount']*100,$user['acquisition_price'],2).'%';

                $user['fu_principal_recovery']=bcdiv($user['expect_amount']*100,$user['acquisition_price'],2).'%';

                $user['remainder_week_back_rate']=bcdiv($user['remainder_week_back_amount']*100,$user['remainder_week_not_due'],2).'%';

                $user['remainder_back_rate']=bcdiv($user['remainder_back_amount']*100,$user['remainder_not_due'],2).'%';

                $data[]=$user;

            }
        }
        $params = $request->input();

        // dd($data);channelAnalysisExport
        ExportService::exportFile($params,$data,ExportService::channel_Analysis_Export);
    }


}
